package com.ws.shop.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Repository;

import com.ws.shop.bean.Page;
import com.ws.shop.dao.SunpicDao;
import com.ws.shop.entity.Accessory;
import com.ws.shop.entity.Mall;
import com.ws.shop.entity.Order;
import com.ws.shop.entity.Paths;
import com.ws.shop.entity.Sunpic;
import com.ws.shop.util.JdbcUtil;
@Repository(value="sunpicDao")
public class SunpicDaoImpl extends BaseDaoImpl implements SunpicDao {
	
	private static Logger log = Logger.getLogger(SunpicDaoImpl.class);
	private String sunpicfiled = " s.id ,s.uid,s.oid ,s.name ,s.aid ,s.speech ,s.comments ,s.state ,s.addtime ,s.reward ,s.link";
	
	/**
	 * 晒东西
	 * @param state
	 * @param limit
	 * @return
	 * @throws SQLException
	 */
	public List<Object> sunpics(final int state , final int limit) throws SQLException{
		final String s = "select " + sunpicfiled + ",a.sn,a.realprice,a.goodsname,u.head,u.addtime as regtime from jvf_sunpic as s "+
"join jvf_auction as a on s.aid = a.id join jvf_user as u on s.uid = u.id where s.state = ? order by s.id desc limit ? " ;
		return this.execute(
			     new ConnectionCallback() {
			      public Object doInConnection(Connection connect) {
			       PreparedStatement psmt = null;
			       ResultSet rs = null;
			       try {
			    	   log.info(" sunpic sql : " + s);
			    	   psmt = connect.prepareStatement(s);
			    	   psmt.setInt(1, state);
			    	   psmt.setInt(2, limit);
			    	   rs = psmt.executeQuery();
			    	   List<Sunpic> lists = new ArrayList<Sunpic>();
			    	   while(rs.next()){
			    		   Sunpic sunpic = new Sunpic();
			    		   long sid = rs.getInt(1);
			    		   int uid = rs.getInt(2);
			    		   sunpic.setId(sid);
			    		   sunpic.setUid(uid);
			    		   sunpic.setOid(rs.getLong(3));
			    		   sunpic.setName(rs.getString(4));
			    		   sunpic.setAid(rs.getLong(5));
			    		   sunpic.setSpeech(rs.getString(6));
			    		   sunpic.setComments(rs.getString(7));
			    		   sunpic.setState(rs.getInt(8));
			    		   sunpic.setAddtime(rs.getTimestamp(9));
			    		   sunpic.setReward(rs.getInt(10));
			    		   sunpic.setLink(rs.getInt(11));
			    		   sunpic.setSn(rs.getString(12));
			    		   sunpic.setRealprice(rs.getDouble(13));
			    		   sunpic.setGoodsname(rs.getString(14));
			    		   sunpic.setHead(rs.getString(15));
			    		   sunpic.setRegtime(rs.getTimestamp(16));
			    		   String s = "select ac.path,ac.thumbnail from  jvf_accessory_relation as ar join jvf_accessory as ac on ar.accessoryid = ac.id where ar.tables = 'Sunpic' and ar.relationid = " + sid;
			    		   log.info("accessory relation sql : " + s);
			    		   Statement stmt = connect.createStatement();
			    		   ResultSet r = stmt.executeQuery(s);
			    		   List<Paths> pathslist = new ArrayList<Paths>();
			    		   while(r.next()){
			    			   Paths paths = new Paths();
			    			   paths.path = r.getString(1);
			    			   paths.thumbnail = r.getString(2);
			    			   pathslist.add(paths);
			    		   }
			    		   JdbcUtil.close(r, stmt, null);
			    		   sunpic.setPaths(pathslist);
			    	   }
			    	   return lists;
			       } catch (Exception e) {
			         e.printStackTrace();
			         log.error("+++++++++++++ sunpics(final int state , final int limit)++++++++++++++++++++",e);
			        return null;
			       } finally {
			        JdbcUtil.close(rs, psmt, null);
			       }
			    }
		});
	}

	
	public List<Object> sunpics(final int state ,final long uid, final int limit) throws SQLException{
		final String s = "select " + sunpicfiled + ",a.sn,a.realprice,a.goodsname,u.head,u.addtime as regtime from jvf_sunpic as s "+
		"join jvf_auction as a on s.aid = a.id join jvf_user as u on s.uid = u.id where s.state = ? and s.uid = ? order by s.id desc limit ? " ;
				return this.execute(
					     new ConnectionCallback() {
					      public Object doInConnection(Connection connect) {
					       try {
					    	   String condition = "s.state = "+state+" and s.uid = "+uid;
					    	   String cs = "";
					    	  return querySunpicData(connect,null,cs,condition,limit) ;
					       } catch (Exception e) {
					         e.printStackTrace();
					         log.error("+++++++++++++ sunpics(final int state ,final long uid, final int limit)++++++++++++++++++++",e);
					        return null;
					       }
					    }
				});
	}
	
	
	public Sunpic sunpics(final int state ,final long id) {
		final String s = "select " + sunpicfiled + ",a.sn,a.realprice,a.goodsname,u.head,u.addtime as regtime from jvf_sunpic as s "+
		"join jvf_auction as a on s.aid = a.id join jvf_user as u on s.uid = u.id where s.state = ? and s.id = ? order by s.id desc " ;
				return this.execute(
					     new ConnectionCallback() {
					      public Object doInConnection(Connection connect) {
					       PreparedStatement psmt = null;
					       ResultSet rs = null;
					       try {
					    	   log.info(" sunpic sql : " + s);
					    	   psmt = connect.prepareStatement(s);
					    	   psmt.setInt(1, state);
					    	   psmt.setLong(2, id);
					    	   rs = psmt.executeQuery();
					    	   if(rs.next()){
					    		   Sunpic sunpic = new Sunpic();
					    		   long sid = rs.getInt(1);
					    		   int uid = rs.getInt(2);
					    		   sunpic.setId(sid);
					    		   sunpic.setUid(uid);
					    		   sunpic.setOid(rs.getLong(3));
					    		   sunpic.setName(rs.getString(4));
					    		   sunpic.setAid(rs.getLong(5));
					    		   sunpic.setSpeech(rs.getString(6));
					    		   sunpic.setComments(rs.getString(7));
					    		   sunpic.setState(rs.getInt(8));
					    		   sunpic.setAddtime(rs.getTimestamp(9));
					    		   sunpic.setReward(rs.getInt(10));
					    		   sunpic.setLink(rs.getInt(11));
					    		   sunpic.setSn(rs.getString(12));
					    		   sunpic.setRealprice(rs.getDouble(13));
					    		   sunpic.setGoodsname(rs.getString(14));
					    		   sunpic.setHead(rs.getString(15));
					    		   sunpic.setRegtime(rs.getTimestamp(16));
					    		   List<Paths> pathslist = getPaths(connect,sid);
					    		   sunpic.setPaths(pathslist);
					    		   return sunpic;
					    	   }
					    	   return null;
					       } catch (Exception e) {
					         e.printStackTrace();
					         log.error("+++++++++++++ sunpics(final int state ,final long uid, final int limit)++++++++++++++++++++",e);
					        return null;
					       } finally {
					        JdbcUtil.close(rs, psmt, null);
					       }
					    }
				});
	}
	
	final String sunpics_page_count = "select count(1) from jvf_sunpic s";
	final String sunpics_page_list = "select s.id,s.aid,s.name,s.speechTitle,s.speech,s.comments,s.addtime,s.reward,a.goodsname,a.goodsprice,a.realprice,a.html,u.head,ac.isupload,ac.thumbnail from jvf_sunpic s left join jvf_auction as a on s.aid = a.id left join jvf_accessory ac on ac.id=s.link left join jvf_user as u on s.uid =u.id";
	final String sunpics_page_order = " order by s.id desc";
	public Page sunpicsPage(final int pageno,int pagesize,final int state){
		StringBuilder query = new StringBuilder(" where 1=1");
		query.append(" and s.state=").append(state);
		return this.findPage(pageno, pagesize, new StringBuilder(sunpics_page_count).append(query).toString(),
				new StringBuilder(sunpics_page_list).append(query).append(sunpics_page_order));
	}
	
	//图片路径
	public List<Paths> getPaths(Connection connect , long sid) throws SQLException{
		   String s = "select ac.path,ac.thumbnail from  jvf_accessory_relation as ar join jvf_accessory as ac on ar.accessoryid = ac.id where ar.tables = 'Sunpic' and ar.relationid = " + sid;
		   Statement mt = connect.createStatement();
		   ResultSet r1 = mt.executeQuery(s);
		   List<Paths> pathslist = new ArrayList<Paths>();
		   while(r1.next()){
			   Paths paths = new Paths();
			   paths.path = r1.getString(1);
			   paths.thumbnail = r1.getString(2);
			   pathslist.add(paths);
		   }
		   JdbcUtil.close(r1, mt, null);
		   return pathslist;
	}
	
	
	public List<Sunpic> querySunpicData(Connection connect ,Page page, String cs, String condition, int limit) throws SQLException{
		boolean is = false;
		Statement stmt = connect.createStatement();
		ResultSet rs = null;
		String s = "select " + sunpicfiled + ",s.speechTitle,a.sn,a.realprice,a.goodsprice,a.goodsname,u.head,u.addtime as regtime from jvf_sunpic as s "+
		"join jvf_auction as a on s.aid = a.id join jvf_user as u on s.uid = u.id where " + condition + "  order by s.id desc " ;
		if(limit != 0){
			s = s + " limit " + limit;
			is = true;
		}else{
			long totalnum = getCount(connect, cs);
			page.setTotalnum(totalnum);
			if(totalnum>0){
				is = true;
				s = s + " limit " + page.getStartrownum() + "," + page.getPagesize();
			}else{
				is = false;
			}
		}
		List<Sunpic> lists = new ArrayList<Sunpic>();
		if(is){
			rs = stmt.executeQuery(s);
	 	    while(rs.next()){
	 		   Sunpic sunpic = new Sunpic();
	 		   long sid = rs.getInt(1);
	 		   int uid = rs.getInt(2);
	 		   sunpic.setId(sid);
	 		   sunpic.setUid(uid);
	 		   sunpic.setOid(rs.getLong(3));
	 		   sunpic.setName(rs.getString(4));
	 		   sunpic.setAid(rs.getLong(5));
	 		   sunpic.setSpeech(rs.getString(6));
	 		   sunpic.setComments(rs.getString(7));
	 		   sunpic.setState(rs.getInt(8));
	 		   sunpic.setAddtime(rs.getTimestamp(9));
	 		   sunpic.setReward(rs.getInt(10));
	 		   sunpic.setLink(rs.getInt(11));
	 		   sunpic.setSpeechTitle(rs.getString(12));
	 		   sunpic.setSn(rs.getString(13));
	 		   sunpic.setRealprice(rs.getDouble(14));
	 		   sunpic.setGoodsprice(rs.getDouble(15));
	 		   sunpic.setGoodsname(rs.getString(16));
	 		   sunpic.setHead(rs.getString(17));
	 		   sunpic.setRegtime(rs.getTimestamp(18));
	 		   List<Paths> pathslist = getPaths(connect,sid);
	 		   sunpic.setPaths(pathslist);
	 		   if(limit == 0 ){
	 			   page.addData(sunpic);
	 		   }else{
	 			   lists.add(sunpic);
	 		   }
	 	   }
		}
		 JdbcUtil.close(rs, stmt, null);
		return lists;
	}
	
	
	/**
	 * 
	 * 获取记录数
	 * @param limit
	 * @return
	 * 
	 */
	public long getCount(long uid,int limit){
		String s ="select count(1) from  jvf_sunpic as s join jvf_auction as a on s.aid = a.id where s.uid = ? and s.state =1";
		return (Long)this.queryForObject(s, new Object[]{uid}, Long.class);
	}
	
	/**
	 * 会员中心 获取已晒图
	 * @param page
	 */
	public List<Map<String,Object>> getFeedBack(Page page,int limit){
		String s="select " + sunpicfiled + ",a.sn,a.goodsname from jvf_sunpic as s join jvf_auction as a on s.aid = a.id order by s.id desc limit " + limit;
		List<Map<String,Object>> sunlist = this.queryForList(s);
		return sunlist;
	}
	
	public void getFreeBack(final Page page,final long uid){
		 this.execute(
					new ConnectionCallback() {
						public Object doInConnection(Connection connect) {
							PreparedStatement psmt = null;
							ResultSet rs = null;
							try {
								String s = "SELECT COUNT(1) FROM  jvf_sunpic WHERE uid = " + uid;
								Statement stmt = connect.createStatement();
								ResultSet r = stmt.executeQuery(s);
								long totalnum = 0;
								if (r.next()) {
									totalnum = r.getLong(1);
									page.setTotalnum(totalnum);
								}
								JdbcUtil.close(r,stmt,null);
								
								s = "select   s.id ,s.uid,s.oid ,s.name ,s.aid ,s.speechTitle,s.speech ,s.comments ,s.state ,s.addtime ,s.reward ,s.link,A.sn,A.goodsname,A.googdsprice  from jvf_sunpic as s "+
								"join jvf_auction as a on s.aid = a.id  where s.uid= ? order by s.id desc limit ?,? ";
								psmt = connect.prepareStatement(s);
								psmt.setLong(1, uid);
								psmt.setLong(2, page.getStartrownum());
								psmt.setInt(3, page.getPagesize());
								rs = psmt.executeQuery();
								while(rs.next()){
									    Sunpic sunpic = new Sunpic();
						    		   long rid = rs.getInt(1);
						    		   int uid = rs.getInt(2);
						    		   sunpic.setId(rid);
						    		   sunpic.setUid(uid);
						    		   sunpic.setOid(rs.getLong(3));
						    		   sunpic.setName(rs.getString(4));
						    		   sunpic.setAid(rs.getLong(5));
						    		   sunpic.setSpeechTitle(rs.getString(6));
						    		   sunpic.setSpeech(rs.getString(7));
						    		   sunpic.setComments(rs.getString(8));
						    		   sunpic.setState(rs.getInt(9));
						    		   sunpic.setAddtime(rs.getTimestamp(10));
						    		   sunpic.setReward(rs.getInt(11));
						    		   sunpic.setLink(rs.getInt(12));
						    		   sunpic.setSn(rs.getString(13));
						    		   sunpic.setGoodsname(rs.getString(14));
						    		   sunpic.setGoodsprice(rs.getDouble(15));
									   page.addData(sunpic);
								}
								return null;
							} catch (Exception e) {
								e.printStackTrace();
								return null;
							} finally {
								JdbcUtil.close(rs, psmt, null);
				      		}
				}
	      });
	}
	
	
	public  List<Sunpic>  getSunpicByUidAndOid(final long uid,final long oid){
		return this.execute(
				new ConnectionCallback() {
					public Object doInConnection(Connection connect) {
						PreparedStatement psmt = null;
						ResultSet rs = null;
						try {
							String s = "select " + sunpicfiled + " from jvf_sunpic as s where s.uid = ? and s.oid = ?";
							psmt = connect.prepareStatement(s);
							psmt.setLong(1, uid);
							psmt.setLong(2, oid);
							rs = psmt.executeQuery();
							List<Sunpic> list = new ArrayList<Sunpic>();
							while(rs.next()){
							   Sunpic sunpic = new Sunpic();
				    		   sunpic.setId(rs.getInt(1));
				    		   sunpic.setUid(rs.getInt(2));
				    		   sunpic.setOid(rs.getLong(3));
				    		   sunpic.setName(rs.getString(4));
				    		   sunpic.setAid(rs.getLong(5));
				    		   sunpic.setSpeech(rs.getString(6));
				    		   sunpic.setComments(rs.getString(7));
				    		   sunpic.setState(rs.getInt(8));
				    		   sunpic.setAddtime(rs.getTimestamp(9));
				    		   sunpic.setReward(rs.getInt(10));
				    		   sunpic.setLink(rs.getInt(11));
				    		   list.add(sunpic);
						    }
							return list;
						} catch (Exception e) {
							e.printStackTrace();
							return null;
						}
			}
      });
	}
	
	
	/**
	 * 保存记录
	 * @param sunpic
	 */
	public int saveSunpic(final long uid,final long id,final int state,final String word,final String[] links){
		 return (Integer)this.execute(
					new ConnectionCallback() {
						public Object doInConnection(Connection connect) {
							Statement stmt = null;
							ResultSet rs = null;
							try {
								connect.setAutoCommit(false);
								String s = "SELECT id,uid,aid,name FROM  jvf_order WHERE uid = " + uid +" and  state = "+ state+" and id = "+ id +" for update";
								stmt = connect.createStatement();
								rs = stmt.executeQuery(s);
								if (rs.next()) {
									long id =rs.getLong(1);
									long uid = rs.getLong(2);
									long aid = rs.getLong(3);
									String name = rs.getString(4);
									JdbcUtil.close(rs,stmt,null);
									stmt = connect.createStatement();
									s = "update jvf_order set state = 4 WHERE uid = " + uid +" and  state = "+ state+" and id = "+ id;
									stmt.addBatch(s);
									s = "insert into jvf_sunpic(uid,oid,name,aid,speech) values("+uid+","+id+",'"+name+"',"+aid+",'"+word+"')";
									stmt.addBatch(s);
									for(int i=0;i<links.length;i++){
										s = "insert into jvf_accessory(type,path,thumbnail) values('img','"+links[i]+"','"+links[i]+"')";
										stmt.addBatch(s);
										s = "insert into jvf_accessory_relation(accessoryid,relationid,table)values(11,11,'Sunpic')"; //调试是必须重新改
										stmt.addBatch(s);
									}
									connect.commit();
									return 0;
								}else{
									connect.rollback();
									return 1;
								}
							} catch (Exception e) {
								e.printStackTrace();
								try {
									connect.rollback();
								} catch (SQLException e1) {
									e1.printStackTrace();
								}
								return -1;
							} finally {
								JdbcUtil.close(rs, stmt, null);
				      		}
				}
	      });
	}
	
	final String findListByPage_count = "select count(1) from jvf_sunpic";
	final String findListByPage_list = "select id,uid,oid,name,aid,speech,comments,state,addtime,reward,link from jvf_sunpic";
	final String findListByPage_order = " order by id desc";
	public Page findListByPage(int pageno, int pagesize, String name, long aid,
			String start, String end) {
		StringBuilder query = new StringBuilder(" where 1=1");
		if(name != null){
			query.append(" and name='").append(name).append("'");
		}
		if(aid > 0){
			query.append(" and aid=").append(aid);
		}
		query.append(" and addtime<='").append(end).append(" 23:59:59'")
			.append(" and addtime>='").append(start).append(" 00:00:00'");
		return this.findPage(pageno, pagesize, new StringBuilder(findListByPage_count).append(query).toString(),
				new StringBuilder(findListByPage_list).append(query).append(findListByPage_order));
	}


	final String findAccessoryListBySunpicId = "select b.id,b.type,b.title,b.path,b.size,b.thumbnail,b.uploadtime,b.isupload from jvf_accessory_relation a left join jvf_accessory b on a.accessoryid = b.id where a.relationid = ? and a.tables = 'Sunpic'";
	public List<Accessory> findAccessoryListBySunpicId(final long id) {
		return this.execute(findAccessoryListBySunpicId, new PreparedStatementCallback<List<Accessory>>(){
			public List<Accessory> doInPreparedStatement(PreparedStatement ps)
					throws SQLException, DataAccessException {
				ResultSet rs = null;
				try{
					List<Accessory> list = new LinkedList<Accessory>();
					ps.setLong(1, id);
					rs = ps.executeQuery();
					Accessory a = null;
					while(rs.next()){
						a = new Accessory();
						a.setId(rs.getInt(1));
						a.setType(rs.getString(2));
						a.setTitle(rs.getString(3));
						a.setPath(rs.getString(4));
						a.setSize(rs.getLong(5));
						a.setThumbnail(rs.getString(6));
						a.setUploadtime(rs.getTimestamp(7));
						a.setIsupload(rs.getInt(8));
						list.add(a);
					}
					return list;
				}finally{
					JdbcUtil.closeResultSet(rs);
				}
			}
		});
	}

	final String findSunpicByAid = "select id,uid,oid,name,speechTitle,speech,comments,state,addtime,reward from jvf_sunpic where aid=?";
	public Sunpic findSunpicByAid(final long aid) {
		return this.query(findSunpicByAid, new ResultSetExtractor<Sunpic>(){
			public Sunpic extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				if(!rs.next())
					return null;
				Sunpic s = new Sunpic();
				s.setAid(aid);
				s.setId(rs.getLong(1));
				s.setUid(rs.getInt(2));
				s.setOid(rs.getLong(3));
				s.setName(rs.getString(4));
				s.setSpeechTitle(rs.getString(5));
				s.setSpeech(rs.getString(6));
				s.setComments(rs.getString(7));
				s.setState(rs.getInt(8));
				s.setAddtime(rs.getTimestamp(9));
				s.setReward(rs.getInt(10));
				return s;
			}
		}, aid);
	}

	
	
	
}
